//
//  HZSQLiteHandler.m
//  Expert
//
//  Created by 刘华舟 on 15/4/8.
//  Copyright (c) 2015年 BLUEBLACK. All rights reserved.
//

#import "HZSQLiteHandler.h"
#import "NSDictionary+SafetyGet.h"
#import "FMDB.h"
#import "HDAppDelegate.h"

// documents的沙盒路径
#define HZDocumentsFilePath(fileName) [[NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:(fileName)]
#define HZJKDRDataDBFileName @"Data.db"

@interface HZSQLiteHandler(){
    FMDatabaseQueue *_databaseQueue;
}

@end

@implementation HZSQLiteHandler

+ (instancetype)defaultHandler{
    static HZSQLiteHandler *_SQLiteHandler = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _SQLiteHandler = [[self alloc] init];
        [_SQLiteHandler setDatabaseName:HZJKDRDataDBFileName];
    });
    return _SQLiteHandler;
}

- (void)loadDatabaseQueue{
    _databaseQueue = [FMDatabaseQueue databaseQueueWithPath:HZDocumentsFilePath(self.databaseName)];
//    HDAppDelegate *appDelegate = (HDAppDelegate *)[UIApplication sharedApplication].delegate;
//    _databaseQueue = appDelegate.fmdbQueue;
}

- (void)setDatabaseName:(NSString *)databaseName{
    _databaseName = databaseName;
    
    // 1.创建地址缓存表
    NSString *createTableSQL = @"create table if not exists t_user_address(id integer primary key autoincrement, result blob, timestamp text);";
    [self createTable:@"t_life_circle" withSQL:createTableSQL];
    
    
//    // 6.创建消息表(朋友圈)
//    createTableSQL = @"create table if not exists t_message(id integer primary key autoincrement, status_id text, username text, message_content text, status_content text, status_urls text, message_type integer, messagedate text, is_read integer);";
//    [self createTable:@"t_message" withSQL:createTableSQL];
}

- (void)createTable:(NSString *)tableName withSQL:(NSString *)sql{
    NSString *querySQL = [NSString stringWithFormat:@"select count(*) as table_count from sqlite_master t where t.type = 'table' and t.name = '%@'", tableName];
    
    [self loadDatabaseQueue];
    
    [_databaseQueue inDatabase:^(FMDatabase *database) {
        BOOL isExist = NO;
        FMResultSet *resultSet = [database executeQuery:querySQL];
        if([resultSet next]){
            isExist = [resultSet intForColumn:@"table_count"];
        }
        
        // 表不存在则创建表
        if(!isExist){
            NSError *error = nil;
            if (![database executeUpdate:sql withErrorAndBindings:&error]) {
                NSLog(@"执行SQL语句: '%@'失败. \n失败原因: %@", sql, [error localizedDescription]);
            }
        }
        [database close];
    }];
    
    [_databaseQueue close];
}

- (BOOL)executeUpdate:(NSString *)sql, ... NS_REQUIRES_NIL_TERMINATION{
    va_list args;
    // 初始化va_list指针变量，即将args指向sql
    va_start(args, sql);
    NSArray *arguments = [self arrayWithVaList:args];
    // 清空参数列表，置指针args无效
    va_end(args);
    
    return [self executeUpdate:sql withArguments:arguments];
}

- (BOOL)executeUpdate:(NSString *)sql withArguments:(NSArray *)arguments{
    __block BOOL isSuccess = NO;
    [self loadDatabaseQueue];
    [_databaseQueue inDatabase:^(FMDatabase *database) {
        isSuccess = [database executeUpdate:sql withArgumentsInArray:arguments];
        [database close];
    }];
    
    [_databaseQueue close];
    return isSuccess;
}

- (HZResultSet *)executeQuery:(NSString *)sql, ... NS_REQUIRES_NIL_TERMINATION{
    va_list args;
    // 初始化va_list指针变量，即将args指向sql
    va_start(args, sql);
    NSArray *arguments = [self arrayWithVaList:args];
    // 清空参数列表，置指针args无效
    va_end(args);
    
    return [self executeQuery:sql withArguments:arguments];
}

- (HZResultSet *)executeQuery:(NSString *)sql withArguments:(NSArray *)arguments{
    __block HZResultSet *resultSet = nil;
    [self loadDatabaseQueue];
    [_databaseQueue inDatabase:^(FMDatabase *database) {
        FMResultSet *rs = [database executeQuery:sql withArgumentsInArray:arguments];
        resultSet = [self convertResultSet:rs];
        [database close];
    }];
    
    [_databaseQueue close];
    return resultSet;
}

- (BOOL)executeStatements:(NSString *)sql{
    __block BOOL isSuccess = NO;
    [self loadDatabaseQueue];
    [_databaseQueue inDatabase:^(FMDatabase *database) {
        isSuccess = [database executeStatements:sql];
        [database close];
    }];
    
    [_databaseQueue close];
    return isSuccess;
}

#pragma -mark 结果集转换

- (HZResultSet *)convertResultSet:(FMResultSet *)resultSet{
    NSMutableArray *resultSetArray = [NSMutableArray array];
    NSInteger columnCount = [resultSet columnCount];
    
    while ([resultSet next]) {
        NSMutableDictionary *valuesMap = [NSMutableDictionary dictionary];
        for(int index = 0; index < columnCount; index ++){
            // 以小写key存储
            NSString *columnName = [[resultSet columnNameForIndex:index] lowercaseString];
            [valuesMap setValue:[resultSet objectForColumnIndex:index] forKey:columnName];
        }
        [resultSetArray addObject:[valuesMap copy]];
    }
    
    return [[HZResultSet alloc] initWithDataArray:[resultSetArray copy] columnNameToIndexMap:resultSet.columnNameToIndexMap];
}

- (NSArray *)arrayWithVaList:(va_list)vaList{
    NSMutableArray *mutableArray = [NSMutableArray array];
    id va = nil;
    while((va = va_arg(vaList,id)))  {
        if(va != nil){
            [mutableArray addObject:va];
        }
    }
    
    return [mutableArray copy];
}

@end




@interface HZResultSet(){
    NSUInteger _rowCount;
    NSUInteger _columnCount;
}

@property (nonatomic, assign) NSInteger currentRow;
@property (nonatomic, strong) NSMutableDictionary *columnIndexToNameMap;
@property (nonatomic, strong) NSDictionary *columnNameToIndexMap;
@property (nonatomic, strong) NSArray *dataArray;

@end

@implementation HZResultSet

static NSString *columnIndexToNameKey = @"ColumnIndexToNameKey";

- (instancetype)initWithDataArray:(NSArray *)dataArray columnNameToIndexMap:(NSDictionary *)columnNameToIndexMap{
    if(self = [super init]){
        _columnCount = 0;
        _rowCount = [dataArray count];
        _currentRow = -1;
        self.columnNameToIndexMap = columnNameToIndexMap;
        self.dataArray = dataArray;
    }
    return self;
}

- (NSMutableDictionary *)columnIndexToNameMap{
    if(_columnIndexToNameMap == nil){
        _columnIndexToNameMap = [NSMutableDictionary dictionary];
    }
    return _columnIndexToNameMap;
}

- (void)setColumnNameToIndexMap:(NSMutableDictionary *)columnNameToIndexMap{
    if(_columnNameToIndexMap == nil){
        _columnCount = [columnNameToIndexMap count];
        _columnNameToIndexMap = columnNameToIndexMap;
        NSArray *values = [columnNameToIndexMap allValues];
        NSArray *keys = [columnNameToIndexMap allKeys];
        for(NSInteger index = 0; index < keys.count; index ++){
            NSString *key = [NSString stringWithFormat:@"%@%@", columnIndexToNameKey, values[index]];
            [self.columnIndexToNameMap setValue:keys[index] forKey:key];
        }
    }
}

- (BOOL)hasNext{
    
    NSInteger currentRow = [[NSNumber numberWithInteger:self.currentRow] integerValue];
    NSInteger rowCount = [[NSNumber numberWithInteger:self.rowCount] integerValue];
    BOOL hasNextRow = (rowCount > 0 && currentRow < rowCount - 1);
    if(hasNextRow){
        self.currentRow ++;
    }
    return hasNextRow;
}

- (NSUInteger)rowCount{
    return _rowCount;
}

- (NSUInteger)columnCount{
    return _columnCount;
}

- (NSArray *)allColumnName{
    return [_columnNameToIndexMap allKeys];
}

- (NSInteger)columnIndexForName:(NSString*)columnName{
    return [_columnNameToIndexMap integerForKey:[columnName lowercaseString]];
}

- (NSString *)columnNameForIndex:(NSInteger)columnIndex{
    NSString *name = [NSString stringWithFormat:@"%@%ld", columnIndexToNameKey, (long)columnIndex];
    return [self.columnIndexToNameMap stringForKey:name];
}

- (NSInteger)intForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary integerForKey:[columnName lowercaseString]];
}

- (NSInteger)intForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self intForColumn:columnName];
}

- (long)longForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return (long)[valueDictionary longLongForKey:[columnName lowercaseString]];
}

- (long)longForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self longForColumn:columnName];
}

- (long long)longLongIntForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary longLongForKey:[columnName lowercaseString]];
}

- (long long)longLongIntForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self longLongIntForColumn:columnName];
}

- (BOOL)boolForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary boolForKey:[columnName lowercaseString]];
}

- (BOOL)boolForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self boolForColumn:columnName];
}

- (double)doubleForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary doubleForKey:[columnName lowercaseString]];
}

- (double)doubleForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self doubleForColumn:columnName];
}

- (NSString *)stringForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary stringForKey:[columnName lowercaseString]];
}

- (NSString *)stringForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self stringForColumn:columnName];
}

- (NSDate *)dateForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary dateForKey:[columnName lowercaseString]];
}

- (NSDate *)dateForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self dateForColumn:columnName];
}

- (NSData *)dataForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary dataForKey:[columnName lowercaseString]];
}

- (NSData *)dataForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self dataForColumn:columnName];
}

- (id)objectForColumnName:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary objectValueForKey:[columnName lowercaseString]];
}

- (id)objectForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self objectForColumnName:columnName];
}

- (BOOL)columnIsNullForIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self columnIsNullColumnName:columnName];
}

- (BOOL)columnIsNullColumnName:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary isNullForKey:[columnName lowercaseString]];
}

- (NSString *)description{
    return [NSString stringWithFormat:@"\n{\n\t\"rowCount\" : \"%lu\", \n\t\"columnCount\" : \"%lu\", \n\t\"data\" : \"%@\"\n}", (unsigned long)[self rowCount], (unsigned long)[self columnCount], [self arrayToString:self.dataArray]];
}

- (NSString *)arrayToString:(NSArray *)array{
    NSMutableString *mutableString = [NSMutableString string];
    [mutableString appendString:@"["];
    for(NSInteger index = 0; index < array.count; index ++){
        NSDictionary *dictionary = array[index];
        if(index != 0){
            [mutableString appendFormat:@"\t%@", [self dictionaryToString:dictionary]];
        }else{
            [mutableString appendString:[self dictionaryToString:dictionary]];
        }
        
        if(index != array.count - 1){
            [mutableString appendString:@", \n"];
        }
    }
    
    [mutableString appendString:@"]"];
    return [mutableString copy];
}

- (NSString *)dictionaryToString:(NSDictionary *)dictionary{
    NSMutableString *mutableString = [NSMutableString string];
    NSArray *keys = [dictionary allKeys];
    [mutableString appendString:@"{\n"];
    for(NSInteger index = 0; index < keys.count; index ++){
        NSString *key = keys[index];
        if([dictionary[key] isKindOfClass:[NSData class]]){
            // 字典中的二进制数据显示为'<blob>'
            [mutableString appendFormat:@"\t\t\"%@\" : <blob>", key];
        }else{
            [mutableString appendFormat:@"\t\t\"%@\" : \"%@\"", key, [dictionary stringForKey:key]];
        }
        
        if(index != keys.count - 1){
            [mutableString appendString:@", \n"];
        }
    }
    
    [mutableString appendString:@"\n\t}"];
    return [mutableString copy];
}

@end